package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import ModelLayer.Customer;
import ModelLayer.Invoice;
import ModelLayer.SalesOrder;

public class DbSaleOrder implements IfDbSaleOrder
{
private Connection con;
 SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

	public DbSaleOrder()
	{
		con = DbConnection.getInstance().getDBcon();
	}
	
	public ArrayList<SalesOrder> getAllSaleOrders(boolean retriveAssociation) 
	{
        return miscWhere("", retriveAssociation);
	}

	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
	    ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();	
		
	    String query =  buildQuery(wClause);
  
        try
        {
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);
	 	
        	while( results.next() )
        	{
        		SalesOrder saleorderObject = new SalesOrder();
        		saleorderObject = buildSalesOrder(results);
                list.add(saleorderObject);
        	}
            stmt.close();
		}
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesOrder saleorderObject = new SalesOrder();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try
		{   //read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
	 			saleorderObject = buildSalesOrder(results);
                            stmt.close();
			}
            else
            {
                saleorderObject = null;
            }
		}	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
		return saleorderObject;
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT OrderNr, Date, DeliveryStatus, DeliveryDate, TotalPrice, ReturnDate, OrderType, CustomerID, InvoiceID FROM SalesOrder";
		
		if (wClause.length()>0)
		{
			query=query+" WHERE "+ wClause;
		}			
		return query;
	}

	private SalesOrder buildSalesOrder(ResultSet results)
    {   
		SalesOrder saleorderObject = new SalesOrder();
        try
        {
        	saleorderObject.setOrderNr(results.getInt("OrderNr"));
        	saleorderObject.setDate(results.getDate("Date"));
        	saleorderObject.setDeliveryStatus(results.getString("DeliveryStatus"));
        	saleorderObject.setDeliveryDate(results.getDate("DeliveryDate"));
        	saleorderObject.setTotalPrice(results.getDouble("TotalPrice"));
        	saleorderObject.setReturnDate(results.getDate("ReturnDate"));
        	saleorderObject.setOrderType(results.getString("OrderType"));
        	saleorderObject.setCustomer(new Customer(results.getInt("CustomerID")));
        	saleorderObject.setInvoice(new Invoice(results.getInt("InvoiceID")));
        }
        catch(Exception e)
        {
            System.out.println("There is a failure in building the SalesOrder");
        }
        return saleorderObject;
    }
	
	public SalesOrder findSalesOrderByOrderNr(int orderNr, boolean retriveAssociation) 
	{
		String wClause = "OrderNr = '" + orderNr + "'";
        System.out.println("SearchSalesOrder " + wClause);
        return singleWhere(wClause, retriveAssociation);
	}

	public int insertSalesOrder(SalesOrder saleorder) throws Exception 
	{
		int rc = -1;
		String parsedDeliveryDate = dateFormat.format(saleorder.getDeliveryDate());
		String parsedDate = dateFormat.format(saleorder.getDate());
		String parsedReturnDate = dateFormat.format(saleorder.getReturnDate());
		String query="INSERT INTO SalesOrder(OrderNr, Date, DeliveryStatus, DeliveryDate, TotalPrice, ReturnDate, OrderType CustomerID, InvoiceID) VALUES('"+
				saleorder.getOrderNr()  + "','"  +
				parsedDate  + "','"  +
				saleorder.getDeliveryStatus() + "','" +
				parsedDeliveryDate + "','" +
				saleorder.getTotalPrice() + "','" +
				parsedReturnDate + "','" +
				saleorder.getCustomer() + "','" +
				saleorder.getInvoice() +  "')";
		try
		{ 
	          Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  rc = stmt.executeUpdate(query);
	          stmt.close();
	    }
	    catch(SQLException ex)
	    {
	        System.out.println("SalesOrder is not created");
	        throw new Exception ("SalesOrder is not inserted correct");
	    }
	    return(rc);
	}

	public int updateSalesOrder(SalesOrder salesorder) 
	{
		SalesOrder salesorderObject = salesorder;
		int lol = -1;

		String query="UPDATE SalesOrder SET "+
		 	  "OrderNr ='"+ salesorderObject.getOrderNr()+ "', " +
		 	  "Date ='"+ salesorderObject.getDate() + "', " +
              "DeliveryStatus ='"+ salesorderObject.getDeliveryStatus() + "', " +
              "DeliveryDate ='"+ salesorderObject.getDeliveryDate() + "' " +
              "TotalPrice ='"+ salesorderObject.getTotalPrice() + "' " +
              "ReturnDate ='"+ salesorderObject.getReturnDate() + "' " +
              "OrderType ='"+ salesorderObject.getOrderType() + "' " +
              "CustomerID ='"+ salesorderObject.getCustomer() + "' " +
              "InvoiceID ='"+ salesorderObject.getInvoice() + "' " +
		      " WHERE OrderNr = '"+ salesorderObject.getOrderNr() + "'";
              System.out.println("Update query:" + query);
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	lol  = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex)
	 	{
	 	 	System.out.println("Update exception in SalesOrder db: " + ex);
	  	}
		return(lol);
	}

	public int deleteSalesOrder(int orderNr)
	{
        int lol = -1;

        String query="DELETE FROM SalesOrder WHERE OrderNr = '"+ orderNr +"'";
        System.out.println(query);
	  	try
	  	{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	lol = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	    catch(Exception ex)
   	    {
	 	  	System.out.println("Delete exception in salesorder db: " + ex);
   	    }
		return(lol);
	}
}